<?php
/*
 * description：
 * author：wh
 * email：
 * createTime：{2019/11/2} {22:38} 
 */

namespace libraries;

class Exportexcel
{
    static function export(){
        require_once (config('app.ROOT_PATH_PRO'))."libraries/PHPExcel/PHPExcel.php";
        $objPHPExcel = new \PHPExcel();

        $objPHPExcel->getProperties()->setCreator("ctos")
            ->setLastModifiedBy("ctos")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");
        //格式设置
        //$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);
        //$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        //$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);
        //$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
        //$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(50);
        //
        ////设置行高度
        //$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(22);
        //
        //$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        //
        ////set font size bold
        //$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);
        //$objPHPExcel->getActiveSheet()->getStyle('A2:E2')->getFont()->setBold(true);

        //边框
        //$objPHPExcel->getActiveSheet()->getStyle('A2:E2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //$objPHPExcel->getActiveSheet()->getStyle('A2:E2')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
        //
        ////设置水平居中
        //$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        //$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //$objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //$objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //$objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //
        ////合并cell
        //$objPHPExcel->getActiveSheet()->mergeCells('A1:J1');


        // set table header content 设置表头
        $column = 'A';
        // foreach ($fields as $key=>$title){
        //     $objPHPExcel->setActiveSheetIndex(0)->setCellValue($column.'1', $title);
        //     $column++;
        // }
        //$objPHPExcel->setActiveSheetIndex(0)
        //    ->setCellValue('A1', '订单数据汇总  时间:'.date('Y-m-d H:i:s'))
        //    ->setCellValue('A2', '订单ID')
        //    ->setCellValue('B2', '商品名称')
        //    ->setCellValue('C2', '价格')
        //    ->setCellValue('D2', '库存')
        //    ->setCellValue('E2', '图片');

        // Miscellaneous glyphs, UTF-8

        // foreach ($fields as $key=>$field){
        //     $column = 'A';
        //     for($i=0;$i<count($list)-1;$i++){
        //         //$objPHPExcel->getActiveSheet(0)->setCellValue($column.($i+3), $list[$i][$field]);
        //         //$objPHPExcel->getActiveSheet(0)->setCellValue('B'.($i+3), $list[$i]['name']);
        //         //$objPHPExcel->getActiveSheet(0)->setCellValue('C'.($i+3), $list[$i]['age']);
        //         //$objPHPExcel->getActiveSheet(0)->setCellValue('D'.($i+3), $list[$i]['email']);
        //         //$objPHPExcel->getActiveSheet(0)->setCellValue('E'.($i+3), $list[$i]['is_deleted']);
        //         ////$objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':J'.($i+3))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //         ////$objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':J'.($i+3))->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
        //         //$objPHPExcel->getActiveSheet()->getRowDimension($i+3)->setRowHeight(16);

        //         dump($column.':'.$i);
        //     }
        //     $column++;
        // }
        //for($i=0;$i<count($list)-1;$i++){
        //    $objPHPExcel->getActiveSheet(0)->setCellValue('A'.($i+3), $list[$i]['id']);
        //    $objPHPExcel->getActiveSheet(0)->setCellValue('B'.($i+3), $list[$i]['name']);
        //    $objPHPExcel->getActiveSheet(0)->setCellValue('C'.($i+3), $list[$i]['age']);
        //    $objPHPExcel->getActiveSheet(0)->setCellValue('D'.($i+3), $list[$i]['email']);
        //    $objPHPExcel->getActiveSheet(0)->setCellValue('E'.($i+3), $list[$i]['is_deleted']);
        //    //$objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':J'.($i+3))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //    //$objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':J'.($i+3))->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
        //    $objPHPExcel->getActiveSheet()->getRowDimension($i+3)->setRowHeight(16);
        //}


        //  sheet命名
        $sheet_name = 'excel_'.date('YmdHis');
        $objPHPExcel->getActiveSheet()->setTitle($sheet_name);


        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);


        // excel头参数
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$sheet_name.'.xlsx"');  //日期为文件名后缀
        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');  //excel5为xls格式，excel2007为xlsx格式

        $objWriter->save('php://output');

    }
    /**
     * [createRemittanceTemplate 输出打款模板excel]
     * @Author QiaoWeiWei
     * @Date   2019-11-29
     * @param  string     $batch [批次号]
     * @return [type]            [description]
     */
    static function createRemittanceTemplate(string $batch, array $cell_arr, array $datas){
        if(empty($batch)){
            return false;
        }
        require_once (config('app.ROOT_PATH_PRO'))."libraries/PHPExcel/PHPExcel.php";
        $objPHPExcel = new \PHPExcel();

        $objPHPExcel->getProperties()->setCreator("system")
            ->setLastModifiedBy("system")
            ->setTitle("Office 2007 XLSX Document")
            ->setSubject("Office 2007 XLSX Document")
            ->setDescription("RemittanceTemplate")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Template");
        // 字体和样式
        $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(12);      //字体大小
        $objPHPExcel->getDefaultStyle()->getFont()->setName('微软雅黑');
        //格式设置
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20); //设置默认行高
        $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension(4)->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(27);
        $objPHPExcel->getActiveSheet()->getStyle('A')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(29);
        $objPHPExcel->getActiveSheet()->getStyle('B')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(38);
        $objPHPExcel->getActiveSheet()->getStyle('C')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(27);
        $objPHPExcel->getActiveSheet()->getStyle('D')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getStyle('E')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getStyle('F')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        // 垂直居中
        $objPHPExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        // 合并单元格
        $objPHPExcel->getActiveSheet()->mergeCells('A1:F1');
        // 单元格内容
        $objPHPExcel->getActiveSheet()
            ->setCellValue('A1', $cell_arr['a1'])
            ->setCellValue('A2', $cell_arr['a2'])
            ->setCellValue('B2', $cell_arr['b2'])
            ->setCellValue('C2', $cell_arr['c2'])
            // ->setCellValue('A3',$batch)
            ->setCellValueExplicit('A3',$batch,\PHPExcel_Cell_DataType::TYPE_STRING)
            ->setCellValue('C3','')
            ->setCellValue('A4', $cell_arr['a4'])
            ->setCellValue('B4', $cell_arr['b4'])
            ->setCellValue('C4', $cell_arr['c4'])
            ->setCellValue('D4', $cell_arr['d4'])
            ->setCellValue('E4', $cell_arr['e4'])
            ->setCellValue('F4', $cell_arr['f4']);

        if($datas){
            $i = 5;
            foreach ($datas as $data){

                $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $data['user_name']);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$i,$data['id_card'],\PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $data['bank_title']);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$i,$data['bank_account'],\PHPExcel_Cell_DataType::TYPE_STRING);
                $quota = sprintf('%.2f',$data['quota']);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$i, $quota,\PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, $data['audit_remark']);
                $i++;
            }
        }
        // 单元格背景色
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ADC0C2');
        $objPHPExcel->getActiveSheet()->getStyle('A2:C2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('FFEB9C');
        $objPHPExcel->getActiveSheet()->getStyle('A4:F4')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('B9F2C4');
        //  sheet命名
        $objPHPExcel->getActiveSheet()->setTitle('打款模板');

        // 合并单元格
        $objPHPExcel->getActiveSheet()->mergeCells('A1:F1');

        $writer_type = config('excel.download_rule.type')['writer_type']; //文件类型
        $ext = config('excel.download_rule.type')['ext']; //文件后缀

        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, $writer_type);  //excel5为xls格式，excel2007为xlsx格式

         // excel头参数
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Type: application/force-download");
        header('Content-Disposition: attachment;filename="'.$batch.'.'.$ext.'"');  //日期为文件名后缀
        header('Cache-Control: max-age=0');
        header('Expires:0');
        header('Pragma:public');


        $objWriter->save('php://output');
    }

    /**
     * [createRemittanceBatchOrders 下载商户批次打款数据文件]
     * @Author QiaoWeiWei
     * @Date   2019-12-19
     * @param  string     $batch        [description]
     * @param  string     $total_count  [description]
     * @param  string     $total_amount [description]
     * @param  array      $data         [description]
     * @return [type]                   [description]
     */
    static function createRemittanceBatchOrders(string $batch,string $total_count,array $data){
        if(empty($batch) || empty($data)){
            return false;
        }
        require_once (config('app.ROOT_PATH_PRO'))."libraries/PHPExcel/PHPExcel.php";
        $objPHPExcel = new \PHPExcel();

        $objPHPExcel->getProperties()->setCreator("system")
            ->setLastModifiedBy("system")
            ->setTitle("Office 2007 XLSX Document")
            ->setSubject("Office 2007 XLSX Document")
            ->setDescription("RemittanceTemplate")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Template");
        // 字体和样式
        $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(12);      //字体大小
        $objPHPExcel->getDefaultStyle()->getFont()->setName('微软雅黑');
        //格式设置
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20); //设置默认行高
        $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension(4)->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(27);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(29);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(27);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(27);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
        // 垂直居中
        $objPHPExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        // 合并单元格
        $objPHPExcel->getActiveSheet()->mergeCells('A1:E1');
        // 单元格内容
        $objPHPExcel->getActiveSheet()->setCellValue('A1', '银行卡账户批量打款模板(单个批次文件最大支持10000条订单,单笔订单最大支持金额可在『商户中心-合作信息』中查看,编辑文件前请将所有单元格格式设置为文本,请勿修改此条信息)')
            ->setCellValue('A2','批次号(与文件名称一致，请勿修改)')
            ->setCellValue('B2','总笔数')
            ->setCellValue('C2','总金额/元(以显示金额汇总)')
            // ->setCellValue('A3',$batch)
            ->setCellValueExplicit('A3',$batch,\PHPExcel_Cell_DataType::TYPE_STRING)
            ->setCellValue('B3',$total_count)
            ->setCellValue('C3','=SUM(D5:D'.($total_count+4).')')
            ->setCellValue('A4','收款账号(个人银行卡号)')
            ->setCellValue('B4','收款户名(真实姓名,必填)')
            ->setCellValue('C4','身份证号(必填)')
            ->setCellValue('D4','打款金额/元(四舍五入至分,必填)')
            ->setCellValue('E4','打款备注(20个字符以内,非必填)');
        // 单元格背景色
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ADC0C2');
        $objPHPExcel->getActiveSheet()->getStyle('A2:C2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('FFEB9C');
        $objPHPExcel->getActiveSheet()->getStyle('A4:E4')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('B9F2C4');

        // 数据填充
        for ($i=0; $i < $total_count; $i++) { 
            $column_id = 5+$i;
            $objPHPExcel->getActiveSheet()
            ->setCellValueExplicit('A'.$column_id,' '.$data[$i]['account_no'],\PHPExcel_Cell_DataType::TYPE_STRING)
            ->setCellValueExplicit('B'.$column_id,' '.$data[$i]['names'],\PHPExcel_Cell_DataType::TYPE_STRING)
            ->setCellValueExplicit('C'.$column_id,' '.$data[$i]['card_no'],\PHPExcel_Cell_DataType::TYPE_STRING)
            ->setCellValueExplicit('D'.$column_id,' '.$data[$i]['amount'],\PHPExcel_Cell_DataType::TYPE_NUMERIC)
            ->setCellValueExplicit('E'.$column_id,' '.$data[$i]['remark'],\PHPExcel_Cell_DataType::TYPE_STRING);
        }        

        //  sheet命名
        $objPHPExcel->getActiveSheet()->setTitle('打款模板');

        // 合并单元格
        $objPHPExcel->getActiveSheet()->mergeCells('A1:E1');

        $writer_type = config('excel.download_rule.type')['writer_type']; //文件类型
        $ext = config('excel.download_rule.type')['ext']; //文件后缀

        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, $writer_type);  //excel5为xls格式，excel2007为xlsx格式

         // excel头参数
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Type: application/force-download");
        header('Content-Disposition: attachment;filename="'.$batch.'.'.$ext.'"');  //日期为文件名后缀
        header('Cache-Control: max-age=0');
        header('Expires:0');
        header('Pragma:public');

        $objWriter->save('php://output');
        exit();
    }

}